package com.ruoyi.contract.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.ruoyi.contract.domain.ConBusiness;
import com.ruoyi.contract.domain.vo.Approver;
import com.ruoyi.contract.domain.vo.CheckContractResult;
import com.ruoyi.contract.domain.vo.ConBusinessDetail;
import com.ruoyi.contract.domain.vo.CusVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * 合同主Mapper接口
 *
 * @author ruoyi
 * @date 2021-09-08
 */
@Repository
public interface ConBusinessMapper extends BaseMapper<ConBusiness> {
	/**
	 * 查询concode
	 *
	 * @param conId 合同主ID
	 * @return 合同主
	 */
	@Select("select CON_CODE from CON_BUSINESS WHERE CON_CODE=#{conCode}")
	public String getConcode(@Param("conCode") String conCode);
	
	/**
	 * 查询合同主
	 *
	 * @param conId 合同主ID
	 * @return 合同主
	 */
	public ConBusinessDetail selectConBusinessById(@Param("conId") Long conId);
	
	
	/**
	 * 查询合同主列表
	 *
	 * @param conBusiness 合同主
	 * @return 合同主集合
	 */
	public List<ConBusiness> selectConBusinessList(ConBusiness conBusiness);

//    /**
//     * 查询合同主列表(多选)
//     *
//     * @param conBusiness 合同主
//     * @return 合同主集合
//     */
//    public List<ConBusiness> selectConBusinessListType(@Param("conBusiness") ConBusiness conBusiness, @Param("conEffTypeSelect") String[] conEffTypeSelect);
	
	/**
	 * 新增合同主
	 *
	 * @param conBusiness 合同主
	 * @return 结果
	 */
	public int insertConBusiness(ConBusiness conBusiness);
	
	/**
	 * 修改合同主
	 *
	 * @param conBusiness 合同主
	 * @return 结果
	 */
	public int updateConBusiness(ConBusiness conBusiness);
	
	/**
	 * 删除合同主
	 *
	 * @param conId 合同主ID
	 * @return 结果
	 */
	public int deleteConBusinessById(String conId);
	
	/**
	 * 批量删除合同主
	 *
	 * @param conIds 需要删除的数据ID
	 * @return 结果
	 */
	public int deleteConBusinessByIds(String[] conIds);
	
	/**
	 * 获取合同列表
	 */
	public List<ConBusiness> listConByWorkInfo(@Param("deptId") Long deptId, @Param("cusId") Long cusId, @Param("workType") String workType, @Param("supplyType") String supplyType, @Param("workDate") Date workDate);
	
	/**
	 * 获取合同列表
	 */
	public List<ConBusiness> listConByWorkInfoto(@Param("deptId") Long deptId, @Param("cusId") Long cusId, @Param("workType") String workType, @Param("supplyType") String supplyType, @Param("workDate") Date workDate, @Param("bindOrderId") String bindOrderId);

//    /**
//     * 获取合同列表(new)
//     */
//    public List<ConBusiness> getConByWork(@Param("bindOrderId") String bindOrderId);
	
	
	/**
	 * 合同绑定检验
	 *
	 * @param conCode
	 * @param billOrderId
	 * @return
	 */
	public CheckContractResult checkContractByConCodeAndOrderId(@Param("conCode") String conCode, @Param("billOrderId") Long billOrderId);
	
	/**
	 * 合同绑定检验
	 *
	 * @param workTime
	 * @param orderId
	 * @param conCode
	 * @param cusId
	 * @param deptId
	 * @param busCode
	 * @return
	 */
	CheckContractResult checkContractByParam(@Param("workTime") String workTime, @Param("orderId") String orderId, @Param("conCode") String conCode, @Param("cusId") String cusId, @Param("deptId") String deptId, @Param("busCode") String busCode);
	
	/**
	 * 获取上次填写的审批人
	 *
	 * @param
	 */
	public List<Approver> getApprover(@Param("userName") String userName, @Param("signType") String signType, @Param("conId") Long conId);
	
	/**
	 * 获取上次填写的审批人(合同)
	 *
	 * @param
	 */
	public List<Approver> getApproverCon(@Param("userName") String userName, @Param("signType") String signType, @Param("conId") Long conId);
	
	/**
	 * 通过sql获取外部对象
	 */
	@Select("SELECT DISTINCT \n" +
			" ccr.CUS_ID , cd.CUS_NAME \n" +
			"FROM \n" +
			" CON_CUS_RELA ccr \n" +
			"LEFT JOIN CON_BUSINESS cb ON cb.CON_ID = ccr.CON_ID \n" +
			"LEFT JOIN SYS_DEPT sd ON sd.DEPT_ID = cb.HANDLE_DEPT_ID \n" +
			"LEFT JOIN CUS_DATA cd ON cd.CUS_ID = ccr.CUS_ID \n" +
			"WHERE ccr.CON_ID = #{conId} AND ccr.CUS_ID != sd.CUS_ID ")
	public List<CusVo> getCusId(Long conId);
	
	
	/**
	 * 通过projectId获取合同列表
	 * SELECT *
	 * FROM (
	 *     SELECT
	 *         ROW_NUMBER() OVER (ORDER BY cb.CON_PRIORITY DESC) AS ROW_NUM,
	 *         cb.CON_ID,
	 *         cb.CON_CODE
	 *     FROM (
	 *         SELECT DISTINCT
	 *             cb.CON_ID ,
	 *             cb.CON_PRIORITY ,
	 *             cb.CON_CODE
	 *         FROM CON_BUSINESS cb
	 *         LEFT JOIN CON_BUSINESS_TYPE cbt ON cb.CON_ID = cbt.CON_ID
	 *         LEFT JOIN CON_OBJ_OUT coo ON cb.CON_ID = coo.CON_ID
	 *         LEFT JOIN GZGCLP_TOS.V_CTMS_CLIENT vcc ON vcc.CUSTOMER_GKEY = coo.CUSTOMER_GKEY
	 *         LEFT JOIN V_PROJECT_TOS vpt ON 1 = 1
	 *         WHERE cb.DEL_FLAG = '0'
	 *             AND vpt.COMPANY_COD = cb.COMPANY_COD
	 *             AND vpt.PROJECT_TYPE = cbt.CON_BUSSINESS_TYPE
	 *             AND vpt.START_TIME >= cb.CON_START_TIME
	 *             AND vpt.END_TIME <= cb.CON_END_TIME
	 *             AND (cb.CON_PRIORITY = 0 OR vcc.CUSTOMER_COD = vpt.CUSTOMER_COD)
	 *             AND vpt.PROJECT_ID = ${PROJECT_ID}
	 *     ) cb
	 * ) WHERE ROW_NUM = 1;
	 *
	 * @param projectId
	 * @return
	 */
//	@Select("SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY cb.CON_PRIORITY DESC) AS ROW_NUM, cb.CON_ID, cb.CON_CODE FROM (SELECT DISTINCT cb.CON_ID , cb.CON_PRIORITY , cb.CON_CODE FROM CON_BUSINESS cb LEFT JOIN CON_BUSINESS_TYPE cbt ON cb.CON_ID = cbt.CON_ID LEFT JOIN CON_OBJ_OUT coo ON cb.CON_ID = coo.CON_ID LEFT JOIN GZGCLP_TOS.V_CTMS_CLIENT vcc ON vcc.CUSTOMER_GKEY = coo.CUSTOMER_GKEY LEFT JOIN V_PROJECT_TOS vpt ON 1 = 1 WHERE cb.DEL_FLAG = '0' AND vpt.COMPANY_COD = cb.COMPANY_COD AND vpt.PROJECT_TYPE = cbt.CON_BUSSINESS_TYPE AND vpt.START_TIME >= cb.CON_START_TIME AND vpt.END_TIME <= cb.CON_END_TIME AND (cb.CON_PRIORITY = 0 OR vcc.CUSTOMER_COD = vpt.CUSTOMER_COD) AND vpt.PROJECT_ID = ${projectId}) cb ) WHERE ROW_NUM = 1")
	@Select("SELECT *\n" +
			"FROM (\n" +
			"    SELECT ROW_NUMBER() OVER (ORDER BY cb.CON_PRIORITY DESC) AS ROW_NUM,\n" +
			"           cb.CON_ID,\n" +
			"           cb.CON_CODE\n" +
			"    FROM (\n" +
			"        SELECT DISTINCT cb.CON_ID,\n" +
			"                        cb.CON_PRIORITY,\n" +
			"                        cb.CON_CODE\n" +
			"        FROM CON_BUSINESS cb\n" +
			"        LEFT JOIN CON_BUSINESS_TYPE cbt ON cb.CON_ID = cbt.CON_ID\n" +
			"        LEFT JOIN CON_OBJ_OUT coo ON cb.CON_ID = coo.CON_ID\n" +
			"        LEFT JOIN GZGCLP_TOS.V_CTMS_CLIENT vcc ON vcc.CUSTOMER_GKEY = coo.CUSTOMER_GKEY\n" +
			"        LEFT JOIN PROJECT_RECORD pr ON 1 = 1\n" +
			"        WHERE cb.DEL_FLAG = '0'\n" +
			"          AND pr.COMPANY_COD = cb.COMPANY_COD\n" +
			"          AND pr.PROJECT_TYPE = cbt.CON_BUSSINESS_TYPE\n" +
			"          AND pr.START_TIME >= cb.CON_START_TIME\n" +
			"          AND pr.END_TIME <= cb.CON_END_TIME\n" +
			"          AND (cb.CON_PRIORITY = 0 OR vcc.CUSTOMER_COD = pr.CUSTOMER_COD)\n" +
			"          AND pr.PROJECT_ID = ${projectId}\n" +
			"    ) cb\n" +
			") WHERE ROW_NUM = 1")
	public List<ConBusiness> listContractByProjectId(@Param("projectId") Long projectId);
}
